using System;
using System.Data;
using System.Data.Common;
using System.Data.SqlTypes;
using System.Data.SqlClient;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using BaHuan.Common;
using BaHuan.Entities;
/*
 * For each DB unit, normally tables, we create a DAO class for it
 * this will enalbe us to do some basic tasks for a DB unit
 * such as Save, Delete, GetAll, GetById....
 * 
 * */
namespace BaHuan.DataLayer
{
	
	class NhanVienDAO:BaseDAO
	{
	
		// save into DB, @id = -1: insert else, update
		public ResultObj SaveNhanVien(NhanVien nhanvien)
        		{
			ResultObj result = new ResultObj();
			result.Result = null;
			SqlConnection conObj = DataConnection.GetSqlConnection();
			SqlCommand cmd = CreateSqlCommand("spAddNhanVien", CommandType.StoredProcedure);
			cmd.CommandType = CommandType.StoredProcedure;
			cmd.CommandText = "spAddNhanVien";
			SqlParameter param = new SqlParameter();
			param = CreateParameter("@Id", DbType.Int32, ParameterDirection.Input, nhanvien.Id);
			cmd.Parameters.Add(param);
			param = CreateParameter("@UserName", DbType.String, ParameterDirection.Input, nhanvien.UserName);
			cmd.Parameters.Add(param);
			param = CreateParameter("@Password", DbType.String, ParameterDirection.Input, nhanvien.Password);
			cmd.Parameters.Add(param);
			param = CreateParameter("@HoTen", DbType.String, ParameterDirection.Input, nhanvien.HoTen);
			cmd.Parameters.Add(param);
			param = CreateParameter("@GioiTinh", DbType.Int32, ParameterDirection.Input, nhanvien.GioiTinh);
			cmd.Parameters.Add(param);
			param = CreateParameter("@NgaySinh", DbType.DateTime, ParameterDirection.Input, nhanvien.NgaySinh);
			cmd.Parameters.Add(param);
			param = CreateParameter("@Email", DbType.String, ParameterDirection.Input, nhanvien.Email);
			cmd.Parameters.Add(param);
			param = CreateParameter("@DienThoaiDiDong", DbType.String, ParameterDirection.Input, nhanvien.DienThoaiDiDong);
			cmd.Parameters.Add(param);
			param = CreateParameter("@DienThoaiNha", DbType.String, ParameterDirection.Input, nhanvien.DienThoaiNha);
			cmd.Parameters.Add(param);
			param = CreateParameter("@CMND", DbType.String, ParameterDirection.Input, nhanvien.CMND);
			cmd.Parameters.Add(param);
			param = CreateParameter("@NgayCapCMND", DbType.DateTime, ParameterDirection.Input, nhanvien.NgayCapCMND);
			cmd.Parameters.Add(param);
			param = CreateParameter("@NoiCapCMND", DbType.String, ParameterDirection.Input, nhanvien.NoiCapCMND);
			cmd.Parameters.Add(param);
			param = CreateParameter("@DiaChiThuongTru", DbType.String, ParameterDirection.Input, nhanvien.DiaChiThuongTru);
			cmd.Parameters.Add(param);
			param = CreateParameter("@DiaChiTamTru", DbType.String, ParameterDirection.Input, nhanvien.DiaChiTamTru);
			cmd.Parameters.Add(param);
			param = CreateParameter("@DanToc", DbType.String, ParameterDirection.Input, nhanvien.DanToc);
			cmd.Parameters.Add(param);
			param = CreateParameter("@TonGiao", DbType.String, ParameterDirection.Input, nhanvien.TonGiao);
			cmd.Parameters.Add(param);
			param = CreateParameter("@HocVan", DbType.String, ParameterDirection.Input, nhanvien.HocVan);
			cmd.Parameters.Add(param);
			param = CreateParameter("@NgayVaoLam", DbType.DateTime, ParameterDirection.Input, nhanvien.NgayVaoLam);
			cmd.Parameters.Add(param);
			param = CreateParameter("@IdChucVu", DbType.Int32, ParameterDirection.Input, nhanvien.IdChucVu);
			cmd.Parameters.Add(param);
            param = CreateParameter("@IsDelete", DbType.Int32, ParameterDirection.Input, nhanvien.IsDelete);
            cmd.Parameters.Add(param);
			param = CreateParameter("@IdChiNhanhCTy", DbType.Int32, ParameterDirection.Input, nhanvien.IdChiNhanhCTy);
			cmd.Parameters.Add(param);
            param = CreateParameter("@ReturnVal", DbType.String, ParameterDirection.ReturnValue, result.ReturnCode);
            cmd.Parameters.Add(param);
			conObj.Open();
			cmd.ExecuteNonQuery();
			conObj.Close();

            result.ReturnCode = Convert.ToInt32(cmd.Parameters["@ReturnVal"].Value);
			return result;
		}
		
		// Get all NhanVien in DB
		public ResultObj GetAllNhanVien( )
        		{
			ResultObj result = new ResultObj();
			result.Result = null;
			List<NhanVien> lst = null;
			SqlCommand cmd = CreateSqlCommand("spGetAllNhanVien", CommandType.StoredProcedure);
			cmd.Connection.Open();
			using (SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection))
			{
			lst = new List<NhanVien>();
			NhanVien item = null;
			// reader all data from DB one by one
			while (reader.Read())
			{
			item = new NhanVien();
			item.Id = Convert.ToInt32(reader["Id"]);
            item.Avatar = Convert.ToString(reader["Avatar"]);
			item.UserName = Convert.ToString(reader["UserName"]);
			item.Password = Convert.ToString(reader["Password"]);
			item.HoTen = Convert.ToString(reader["HoTen"]);
			item.GioiTinh = Convert.ToInt32(reader["GioiTinh"]);
			item.NgaySinh = Convert.ToDateTime(reader["NgaySinh"]);
			item.Email = Convert.ToString(reader["Email"]);
			item.DienThoaiDiDong = Convert.ToString(reader["DienThoaiDiDong"]);
			item.DienThoaiNha = Convert.ToString(reader["DienThoaiNha"]);
			item.CMND = Convert.ToString(reader["CMND"]);
			item.NgayCapCMND = Convert.ToDateTime(reader["NgayCapCMND"]);
			item.NoiCapCMND = Convert.ToString(reader["NoiCapCMND"]);
			item.DiaChiThuongTru = Convert.ToString(reader["DiaChiThuongTru"]);
			item.DiaChiTamTru = Convert.ToString(reader["DiaChiTamTru"]);
			item.DanToc = Convert.ToString(reader["DanToc"]);
			item.TonGiao = Convert.ToString(reader["TonGiao"]);
			item.HocVan = Convert.ToString(reader["HocVan"]);
			item.NgayVaoLam = Convert.ToDateTime(reader["NgayVaoLam"]);
			item.IdChucVu = Convert.ToInt32(reader["IdChucVu"]);
            item.IsDelete = Convert.ToInt32(reader["IsDelete"]);
			item.IdChiNhanhCTy = Convert.ToInt32(reader["IdChiNhanhCTy"]);
			lst.Add(item);
			}
			}
			result.Result = lst;
			result.ReturnCode = 1;
			
			return result;
		}

        //check username , password
        public ResultObj GetAllNhanVienByUserNamePassword(NhanVien nhanvien)
        {
            ResultObj result = new ResultObj();
            result.Result = null;
            List<NhanVien> lst = null;
            SqlCommand cmd = CreateSqlCommand("spGetAllNhanVienByUserNamePassword", CommandType.StoredProcedure);
            SqlParameter param = CreateParameter("@UserName", DbType.String, ParameterDirection.Input, nhanvien.UserName);
            cmd.Parameters.Add(param);
            param = CreateParameter("@Password", DbType.String, ParameterDirection.Input, nhanvien.Password);
            cmd.Parameters.Add(param);
            cmd.Connection.Open();
            using (SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection))
            {
                lst = new List<NhanVien>();
                NhanVien item = null;
                // reader all data from DB one by one
                while (reader.Read())
                {
                    item = new NhanVien();
                    item.Id = Convert.ToInt32(reader["Id"]);
                    item.Avatar = Convert.ToString(reader["Avatar"]);
                    item.UserName = Convert.ToString(reader["UserName"]);
                    item.Password = Convert.ToString(reader["Password"]);
                    item.HoTen = Convert.ToString(reader["HoTen"]);
                    item.GioiTinh = Convert.ToInt32(reader["GioiTinh"]);
                    item.NgaySinh = Convert.ToDateTime(reader["NgaySinh"]);
                    item.Email = Convert.ToString(reader["Email"]);
                    item.DienThoaiDiDong = Convert.ToString(reader["DienThoaiDiDong"]);
                    item.DienThoaiNha = Convert.ToString(reader["DienThoaiNha"]);
                    item.CMND = Convert.ToString(reader["CMND"]);
                    item.NgayCapCMND = Convert.ToDateTime(reader["NgayCapCMND"]);
                    item.NoiCapCMND = Convert.ToString(reader["NoiCapCMND"]);
                    item.DiaChiThuongTru = Convert.ToString(reader["DiaChiThuongTru"]);
                    item.DiaChiTamTru = Convert.ToString(reader["DiaChiTamTru"]);
                    item.DanToc = Convert.ToString(reader["DanToc"]);
                    item.TonGiao = Convert.ToString(reader["TonGiao"]);
                    item.HocVan = Convert.ToString(reader["HocVan"]);
                    item.NgayVaoLam = Convert.ToDateTime(reader["NgayVaoLam"]);
                    item.IdChucVu = Convert.ToInt32(reader["IdChucVu"]);
                    item.IsDelete = Convert.ToInt32(reader["IsDelete"]);
                    item.IdChiNhanhCTy = Convert.ToInt32(reader["IdChiNhanhCTy"]);
                    lst.Add(item);
                }
            }
            result.Result = lst;
            result.ReturnCode = 1;

            return result;
        }


		// Get all NhanVien in DB with condition
		public ResultObj GetAllNhanVienByID(NhanVien nhanvien)
        		{
			ResultObj result = new ResultObj();
			result.Result = null;
			List<NhanVien> lst = null;
			SqlCommand cmd = CreateSqlCommand("spGetAllNhanVienByID", CommandType.StoredProcedure);
			SqlParameter param = CreateParameter("@Id", DbType.Int32, ParameterDirection.Input, nhanvien.Id);
			cmd.Parameters.Add(param);
			cmd.Connection.Open();
			using (SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection))
			{
			lst = new List<NhanVien>();
			NhanVien item = null;
			// reader all data from DB one by one
			while (reader.Read())
			{
			item = new NhanVien();
			item.Id = Convert.ToInt32(reader["Id"]);
            item.Avatar = Convert.ToString(reader["Avatar"]);
			item.UserName = Convert.ToString(reader["UserName"]);
			item.Password = Convert.ToString(reader["Password"]);
			item.HoTen = Convert.ToString(reader["HoTen"]);
			item.GioiTinh = Convert.ToInt32(reader["GioiTinh"]);
			item.NgaySinh = Convert.ToDateTime(reader["NgaySinh"]);
			item.Email = Convert.ToString(reader["Email"]);
			item.DienThoaiDiDong = Convert.ToString(reader["DienThoaiDiDong"]);
			item.DienThoaiNha = Convert.ToString(reader["DienThoaiNha"]);
			item.CMND = Convert.ToString(reader["CMND"]);
			item.NgayCapCMND = Convert.ToDateTime(reader["NgayCapCMND"]);
			item.NoiCapCMND = Convert.ToString(reader["NoiCapCMND"]);
			item.DiaChiThuongTru = Convert.ToString(reader["DiaChiThuongTru"]);
			item.DiaChiTamTru = Convert.ToString(reader["DiaChiTamTru"]);
			item.DanToc = Convert.ToString(reader["DanToc"]);
			item.TonGiao = Convert.ToString(reader["TonGiao"]);
			item.HocVan = Convert.ToString(reader["HocVan"]);
			item.NgayVaoLam = Convert.ToDateTime(reader["NgayVaoLam"]);
			item.IdChucVu = Convert.ToInt32(reader["IdChucVu"]);
            item.IsDelete = Convert.ToInt32(reader["IsDelete"]);
			item.IdChiNhanhCTy = Convert.ToInt32(reader["IdChiNhanhCTy"]);
			lst.Add(item);
			}
			}
			result.Result = lst;
			result.ReturnCode = 1;
			
			return result;
		}
		
		// Delete a NhanVien out of DB with condition
		public ResultObj DeleteNhanVien(NhanVien nhanvien)
        		{
			ResultObj result = new ResultObj();
			result.Result = null;
			SqlConnection conObj = DataConnection.GetSqlConnection();
			SqlCommand cmd = CreateSqlCommand("spDeleteNhanVien", CommandType.StoredProcedure);
			cmd.CommandType = CommandType.StoredProcedure;
			cmd.CommandText = "spDeleteNhanVien";
			SqlParameter param = CreateParameter("@Id", DbType.Int32, ParameterDirection.Input, nhanvien.Id);
			cmd.Parameters.Add(param);
			conObj.Open();
			result.ReturnCode = cmd.ExecuteNonQuery();
			conObj.Close();
			
			return result;
		}
		
		// Get info of ChiNhanhCongTy from DB with condition
		public ResultObj GetChiNhanhCongTy(int IdChiNhanhCongTy)
        		{
			ResultObj result = new ResultObj();
			result.Result = null;
			List<ChiNhanhCongTy> lst = null;
			SqlCommand cmd = CreateSqlCommand("spGetChiNhanhCongTy", CommandType.StoredProcedure);
			SqlParameter param = CreateParameter("@Id", DbType.Int32, ParameterDirection.Input, IdChiNhanhCongTy);
			cmd.Parameters.Add(param);
			cmd.Connection.Open();
			using (SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection))
			{
			lst = new List<ChiNhanhCongTy>();
			ChiNhanhCongTy item = null;
			// reader all data from DB one by one
			while (reader.Read())
			{
			item = new ChiNhanhCongTy();
			item.Id = Convert.ToInt32(reader["Id"]);
			item.TenChiNhanh = Convert.ToString(reader["TenChiNhanh"]);
			item.DiaChiChiNhanh = Convert.ToString(reader["DiaChiChiNhanh"]);
			item.SoDienThoai = Convert.ToString(reader["SoDienThoai"]);
			lst.Add(item);
			}
			}
			result.Result = lst;
			result.ReturnCode = 1;
			
			return result;
		}
		
		// Get info of ChucVu from DB with condition
		public ResultObj GetChucVu(int IdChucVu)
        		{
			ResultObj result = new ResultObj();
			result.Result = null;
			List<ChucVu> lst = null;
			SqlCommand cmd = CreateSqlCommand("spGetChucVu", CommandType.StoredProcedure);
			SqlParameter param = CreateParameter("@Id", DbType.Int32, ParameterDirection.Input, IdChucVu);
			cmd.Parameters.Add(param);
			cmd.Connection.Open();
			using (SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection))
			{
			lst = new List<ChucVu>();
			ChucVu item = null;
			// reader all data from DB one by one
			while (reader.Read())
			{
			item = new ChucVu();
			item.Id = Convert.ToInt32(reader["Id"]);
			item.TenChucVu = Convert.ToString(reader["TenChucVu"]);
			lst.Add(item);
			}
			}
			result.Result = lst;
			result.ReturnCode = 1;
			
			return result;
		}
					
	}
}
